{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "940ee257-a378-4359-9c38-efdb80c847ac",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "原始代码: 123260 -> 转换后: sz123260\n",
      "原始代码: 113570 -> 转换后: sh113570\n",
      "原始代码: 118059 -> 转换后: sh118059\n",
      "原始代码: 110099 -> 转换后: sh110099\n",
      "原始代码: 127111 -> 转换后: sz127111\n"
     ]
    }
   ],
   "source": [
    "def convert_bond_symbol_with_market(bond_code):\n",
    "    \"\"\"\n",
    "    更健壮的债券代码转换函数，处理各种异常情况\n",
    "    \"\"\"\n",
    "    if bond_code is None:\n",
    "        return None\n",
    "    \n",
    "    try:\n",
    "        bond_code = str(bond_code).strip()\n",
    "        \n",
    "        # 如果已经包含市场标签，直接返回\n",
    "        if bond_code.startswith(('sh', 'sz')):\n",
    "            return bond_code.lower()\n",
    "        \n",
    "        # 清理可能的空格或其他字符\n",
    "        bond_code = ''.join(filter(str.isdigit, bond_code))\n",
    "        \n",
    "        if len(bond_code) != 6:\n",
    "            return None, None\n",
    "        \n",
    "        # 判断市场\n",
    "        if bond_code[:2] == '11':\n",
    "            return f\"sh{bond_code}\", \"sh\"\n",
    "        elif bond_code[:2] == '12':\n",
    "            return f\"sz{bond_code}\", \"sz\"\n",
    "        else:\n",
    "            return None\n",
    "            \n",
    "    except Exception as e:\n",
    "        print(f\"转换债券代码时出错: {bond_code}, 错误: {e}\")\n",
    "        return None, None\n",
    "\n",
    "# 测试函数\n",
    "test_codes = ['123260', '113570', '118059', '110099', '127111']\n",
    "for code in test_codes:\n",
    "    result = convert_bond_symbol_robust(code)\n",
    "    print(f\"原始代码: {code} -> 转换后: {result}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "b2d5faa0-63c9-466b-b571-741d46c4b0ac",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "postgresql://hbu:********@127.0.0.1:2345/hbu\n",
      "环境变量加载完成！\n",
      "数据库连接引擎创建成功！\n"
     ]
    }
   ],
   "source": [
    "# 导入必要的库\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import sys\n",
    "from sqlalchemy import create_engine\n",
    "import psycopg2\n",
    "from dotenv import load_dotenv\n",
    "\n",
    "# 加载环境变量\n",
    "load_dotenv()\n",
    "\n",
    "# 从环境变量获取数据库连接信息，如果不存在则使用默认值\n",
    "driver=os.getenv('DRIVER', 'postgresql')\n",
    "user=os.getenv('PGUSER', None)\n",
    "password=os.getenv('PGPASSWORD', None)\n",
    "host=os.getenv('PGHOST', None)\n",
    "port=os.getenv('PGPORT', None)\n",
    "database=os.getenv('PGDATABASE', 'postgres')\n",
    "\n",
    "# schema is used for postgres, similiar with database level in MySQL\n",
    "schema=os.getenv('SCHEMA',\"public\")\n",
    "\n",
    "DATABASE_URL = f\"{driver}://{user}:{password}@{host}:{port}/{database}\"\n",
    "\n",
    "if user is not None and password is not None:\n",
    "    print(f\"{driver}://{user}:********@{host}:{port}/{database}\")\n",
    "else:\n",
    "    print('非法的数据库连接URL')\n",
    "    sys.exit(1)\n",
    "print('环境变量加载完成！')\n",
    "\n",
    "# 创建数据库连接引擎\n",
    "try:\n",
    "    engine = create_engine(DATABASE_URL)\n",
    "    print('数据库连接引擎创建成功！')\n",
    "except Exception as e:\n",
    "    print(f'创建数据库连接引擎失败: {e}')\n",
    "    raise\n",
    "# 从PostgreSQL数据库读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "fdbb1eaf-8289-4ed5-886c-a376ffaad395",
   "metadata": {},
   "outputs": [],
   "source": [
    "#import matplotlib.pyplot as plt\n",
    "\n",
    "from datetime import datetime\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "# 从数据库读取数据\n",
    "query = f'SELECT * FROM bond_zh_cov'\n",
    "\n",
    "df = pd.read_sql(query, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "11a4c783-fcf2-4719-ba16-9ba631bb02c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "data=df[['债券代码','债券简称','申购日期','申购代码', '正股代码', '正股简称', '发行规模', '上市时间', '信用评级']].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "d757b6b6-2403-4a0e-8292-9f607b101ecf",
   "metadata": {},
   "outputs": [],
   "source": [
    "data[['symbol', 'market']] = df['债券代码'].apply(\n",
    "    lambda x: pd.Series(convert_bond_symbol_with_market(x))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "88802558-918c-434b-961f-1e50fb017c7d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "market\n",
       "sz    539\n",
       "sh    444\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['market'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "ce21b853-7534-4984-baf2-421b3a07433f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>债券代码</th>\n",
       "      <th>债券简称</th>\n",
       "      <th>申购日期</th>\n",
       "      <th>申购代码</th>\n",
       "      <th>正股代码</th>\n",
       "      <th>正股简称</th>\n",
       "      <th>发行规模</th>\n",
       "      <th>上市时间</th>\n",
       "      <th>信用评级</th>\n",
       "      <th>symbol</th>\n",
       "      <th>market</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123260</td>\n",
       "      <td>卓镁转债</td>\n",
       "      <td>2025-11-07</td>\n",
       "      <td>371398</td>\n",
       "      <td>301398</td>\n",
       "      <td>星源卓镁</td>\n",
       "      <td>4.500000</td>\n",
       "      <td>None</td>\n",
       "      <td>A+</td>\n",
       "      <td>sz123260</td>\n",
       "      <td>sz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>118059</td>\n",
       "      <td>颀中转债</td>\n",
       "      <td>2025-11-03</td>\n",
       "      <td>718352</td>\n",
       "      <td>688352</td>\n",
       "      <td>颀中科技</td>\n",
       "      <td>8.500000</td>\n",
       "      <td>None</td>\n",
       "      <td>AA+</td>\n",
       "      <td>sh118059</td>\n",
       "      <td>sh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>123259</td>\n",
       "      <td>锦浪转02</td>\n",
       "      <td>2025-10-17</td>\n",
       "      <td>370763</td>\n",
       "      <td>300763</td>\n",
       "      <td>锦浪科技</td>\n",
       "      <td>16.765838</td>\n",
       "      <td>2025-11-06</td>\n",
       "      <td>AA</td>\n",
       "      <td>sz123259</td>\n",
       "      <td>sz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>110099</td>\n",
       "      <td>福能转债</td>\n",
       "      <td>2025-10-13</td>\n",
       "      <td>733483</td>\n",
       "      <td>600483</td>\n",
       "      <td>福能股份</td>\n",
       "      <td>38.020000</td>\n",
       "      <td>2025-10-30</td>\n",
       "      <td>AA+</td>\n",
       "      <td>sh110099</td>\n",
       "      <td>sh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>113699</td>\n",
       "      <td>金25转债</td>\n",
       "      <td>2025-09-26</td>\n",
       "      <td>754979</td>\n",
       "      <td>603979</td>\n",
       "      <td>金诚信</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>2025-10-27</td>\n",
       "      <td>AA</td>\n",
       "      <td>sh113699</td>\n",
       "      <td>sh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>983</th>\n",
       "      <td>110227</td>\n",
       "      <td>赤化转债</td>\n",
       "      <td>2007-10-10</td>\n",
       "      <td>733227</td>\n",
       "      <td>600227</td>\n",
       "      <td>赤天化</td>\n",
       "      <td>4.500000</td>\n",
       "      <td>2007-10-23</td>\n",
       "      <td>AAA</td>\n",
       "      <td>sh110227</td>\n",
       "      <td>sh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>984</th>\n",
       "      <td>126006</td>\n",
       "      <td>07深高债</td>\n",
       "      <td>2007-10-09</td>\n",
       "      <td>733548</td>\n",
       "      <td>600548</td>\n",
       "      <td>深高速</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>2007-10-30</td>\n",
       "      <td>AAA</td>\n",
       "      <td>sz126006</td>\n",
       "      <td>sz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>985</th>\n",
       "      <td>110971</td>\n",
       "      <td>恒源转债</td>\n",
       "      <td>2007-09-24</td>\n",
       "      <td>733971</td>\n",
       "      <td>600971</td>\n",
       "      <td>恒源煤电</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>2007-10-12</td>\n",
       "      <td>AAA</td>\n",
       "      <td>sh110971</td>\n",
       "      <td>sh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>986</th>\n",
       "      <td>110567</td>\n",
       "      <td>山鹰转债</td>\n",
       "      <td>2007-09-05</td>\n",
       "      <td>733567</td>\n",
       "      <td>600567</td>\n",
       "      <td>山鹰国际</td>\n",
       "      <td>4.700000</td>\n",
       "      <td>2007-09-17</td>\n",
       "      <td>AA</td>\n",
       "      <td>sh110567</td>\n",
       "      <td>sh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>987</th>\n",
       "      <td>110026</td>\n",
       "      <td>中海转债</td>\n",
       "      <td>2007-07-02</td>\n",
       "      <td>733026</td>\n",
       "      <td>600026</td>\n",
       "      <td>中远海能</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>2007-07-12</td>\n",
       "      <td>AAA</td>\n",
       "      <td>sh110026</td>\n",
       "      <td>sh</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>988 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       债券代码   债券简称        申购日期    申购代码    正股代码  正股简称       发行规模        上市时间  \\\n",
       "0    123260   卓镁转债  2025-11-07  371398  301398  星源卓镁   4.500000        None   \n",
       "1    118059   颀中转债  2025-11-03  718352  688352  颀中科技   8.500000        None   \n",
       "2    123259  锦浪转02  2025-10-17  370763  300763  锦浪科技  16.765838  2025-11-06   \n",
       "3    110099   福能转债  2025-10-13  733483  600483  福能股份  38.020000  2025-10-30   \n",
       "4    113699  金25转债  2025-09-26  754979  603979   金诚信  20.000000  2025-10-27   \n",
       "..      ...    ...         ...     ...     ...   ...        ...         ...   \n",
       "983  110227   赤化转债  2007-10-10  733227  600227   赤天化   4.500000  2007-10-23   \n",
       "984  126006  07深高债  2007-10-09  733548  600548   深高速  15.000000  2007-10-30   \n",
       "985  110971   恒源转债  2007-09-24  733971  600971  恒源煤电   4.000000  2007-10-12   \n",
       "986  110567   山鹰转债  2007-09-05  733567  600567  山鹰国际   4.700000  2007-09-17   \n",
       "987  110026   中海转债  2007-07-02  733026  600026  中远海能  20.000000  2007-07-12   \n",
       "\n",
       "    信用评级    symbol market  \n",
       "0     A+  sz123260     sz  \n",
       "1    AA+  sh118059     sh  \n",
       "2     AA  sz123259     sz  \n",
       "3    AA+  sh110099     sh  \n",
       "4     AA  sh113699     sh  \n",
       "..   ...       ...    ...  \n",
       "983  AAA  sh110227     sh  \n",
       "984  AAA  sz126006     sz  \n",
       "985  AAA  sh110971     sh  \n",
       "986   AA  sh110567     sh  \n",
       "987  AAA  sh110026     sh  \n",
       "\n",
       "[988 rows x 11 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "4f67d5bb-e5cc-4b84-8fba-328f07428411",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "988"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(data['market'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "0f6e3f27-e96b-44fd-a82e-4329c730316e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>债券代码</th>\n",
       "      <th>债券简称</th>\n",
       "      <th>申购日期</th>\n",
       "      <th>申购代码</th>\n",
       "      <th>正股代码</th>\n",
       "      <th>正股简称</th>\n",
       "      <th>发行规模</th>\n",
       "      <th>上市时间</th>\n",
       "      <th>信用评级</th>\n",
       "      <th>symbol</th>\n",
       "      <th>market</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>453</th>\n",
       "      <td>404005</td>\n",
       "      <td>普利退债</td>\n",
       "      <td>2021-02-09</td>\n",
       "      <td>370630</td>\n",
       "      <td>400266</td>\n",
       "      <td>R普利1</td>\n",
       "      <td>8.5000</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>542</th>\n",
       "      <td>404004</td>\n",
       "      <td>汇车退债</td>\n",
       "      <td>2020-08-18</td>\n",
       "      <td>733297</td>\n",
       "      <td>400245</td>\n",
       "      <td>R汇车1</td>\n",
       "      <td>33.7000</td>\n",
       "      <td>2024-11-01</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>638</th>\n",
       "      <td>404002</td>\n",
       "      <td>搜特退债</td>\n",
       "      <td>2020-03-12</td>\n",
       "      <td>072503</td>\n",
       "      <td>400198</td>\n",
       "      <td>R搜特1</td>\n",
       "      <td>8.0000</td>\n",
       "      <td>2023-10-20</td>\n",
       "      <td>C</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>686</th>\n",
       "      <td>404003</td>\n",
       "      <td>鸿达退债</td>\n",
       "      <td>2019-12-16</td>\n",
       "      <td>072002</td>\n",
       "      <td>400207</td>\n",
       "      <td>R鸿达1</td>\n",
       "      <td>24.2678</td>\n",
       "      <td>2024-05-24</td>\n",
       "      <td>C</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>819</th>\n",
       "      <td>404001</td>\n",
       "      <td>蓝盾退债</td>\n",
       "      <td>2018-08-13</td>\n",
       "      <td>370297</td>\n",
       "      <td>400194</td>\n",
       "      <td>R蓝盾1</td>\n",
       "      <td>5.3800</td>\n",
       "      <td>2023-09-18</td>\n",
       "      <td>CC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       债券代码  债券简称        申购日期    申购代码    正股代码  正股简称     发行规模        上市时间  \\\n",
       "453  404005  普利退债  2021-02-09  370630  400266  R普利1   8.5000        None   \n",
       "542  404004  汇车退债  2020-08-18  733297  400245  R汇车1  33.7000  2024-11-01   \n",
       "638  404002  搜特退债  2020-03-12  072503  400198  R搜特1   8.0000  2023-10-20   \n",
       "686  404003  鸿达退债  2019-12-16  072002  400207  R鸿达1  24.2678  2024-05-24   \n",
       "819  404001  蓝盾退债  2018-08-13  370297  400194  R蓝盾1   5.3800  2023-09-18   \n",
       "\n",
       "     信用评级 symbol market  \n",
       "453  None    NaN    NaN  \n",
       "542     A    NaN    NaN  \n",
       "638     C    NaN    NaN  \n",
       "686     C    NaN    NaN  \n",
       "819    CC    NaN    NaN  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[~data['market'].isin(['sh', 'sz'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "95d5f691-4308-427f-b2ce-481426958a61",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "market\n",
       "sz    539\n",
       "sh    444\n",
       "q       5\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['market'] = data['market'].apply(lambda x: 'q' if pd.isna(x) else x)\n",
    "data['market'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "807b931c-cdf0-4d30-8c37-0acca1d130f1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>债券代码</th>\n",
       "      <th>债券简称</th>\n",
       "      <th>申购日期</th>\n",
       "      <th>申购代码</th>\n",
       "      <th>正股代码</th>\n",
       "      <th>正股简称</th>\n",
       "      <th>发行规模</th>\n",
       "      <th>上市时间</th>\n",
       "      <th>信用评级</th>\n",
       "      <th>symbol</th>\n",
       "      <th>market</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>453</th>\n",
       "      <td>404005</td>\n",
       "      <td>普利退债</td>\n",
       "      <td>2021-02-09</td>\n",
       "      <td>370630</td>\n",
       "      <td>400266</td>\n",
       "      <td>R普利1</td>\n",
       "      <td>8.5000</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>542</th>\n",
       "      <td>404004</td>\n",
       "      <td>汇车退债</td>\n",
       "      <td>2020-08-18</td>\n",
       "      <td>733297</td>\n",
       "      <td>400245</td>\n",
       "      <td>R汇车1</td>\n",
       "      <td>33.7000</td>\n",
       "      <td>2024-11-01</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>638</th>\n",
       "      <td>404002</td>\n",
       "      <td>搜特退债</td>\n",
       "      <td>2020-03-12</td>\n",
       "      <td>072503</td>\n",
       "      <td>400198</td>\n",
       "      <td>R搜特1</td>\n",
       "      <td>8.0000</td>\n",
       "      <td>2023-10-20</td>\n",
       "      <td>C</td>\n",
       "      <td>NaN</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>686</th>\n",
       "      <td>404003</td>\n",
       "      <td>鸿达退债</td>\n",
       "      <td>2019-12-16</td>\n",
       "      <td>072002</td>\n",
       "      <td>400207</td>\n",
       "      <td>R鸿达1</td>\n",
       "      <td>24.2678</td>\n",
       "      <td>2024-05-24</td>\n",
       "      <td>C</td>\n",
       "      <td>NaN</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>819</th>\n",
       "      <td>404001</td>\n",
       "      <td>蓝盾退债</td>\n",
       "      <td>2018-08-13</td>\n",
       "      <td>370297</td>\n",
       "      <td>400194</td>\n",
       "      <td>R蓝盾1</td>\n",
       "      <td>5.3800</td>\n",
       "      <td>2023-09-18</td>\n",
       "      <td>CC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       债券代码  债券简称        申购日期    申购代码    正股代码  正股简称     发行规模        上市时间  \\\n",
       "453  404005  普利退债  2021-02-09  370630  400266  R普利1   8.5000        None   \n",
       "542  404004  汇车退债  2020-08-18  733297  400245  R汇车1  33.7000  2024-11-01   \n",
       "638  404002  搜特退债  2020-03-12  072503  400198  R搜特1   8.0000  2023-10-20   \n",
       "686  404003  鸿达退债  2019-12-16  072002  400207  R鸿达1  24.2678  2024-05-24   \n",
       "819  404001  蓝盾退债  2018-08-13  370297  400194  R蓝盾1   5.3800  2023-09-18   \n",
       "\n",
       "     信用评级 symbol market  \n",
       "453  None    NaN      q  \n",
       "542     A    NaN      q  \n",
       "638     C    NaN      q  \n",
       "686     C    NaN      q  \n",
       "819    CC    NaN      q  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.query(\"market=='q'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "c85dcb0b-3a12-41f0-8b08-094bafbbd81f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "信用评级\n",
       "AA      272\n",
       "AA-     264\n",
       "A+      156\n",
       "AA+     110\n",
       "AAA     100\n",
       "A        44\n",
       "A-       16\n",
       "CC        6\n",
       "BBB+      3\n",
       "BBB       3\n",
       "B-        3\n",
       "CCC       3\n",
       "C         3\n",
       "B+        1\n",
       "BB        1\n",
       "BB+       1\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['信用评级'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "d85ff5eb-46d4-4049-98f7-d24789bbb656",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>债券代码</th>\n",
       "      <th>债券简称</th>\n",
       "      <th>申购日期</th>\n",
       "      <th>申购代码</th>\n",
       "      <th>正股代码</th>\n",
       "      <th>正股简称</th>\n",
       "      <th>发行规模</th>\n",
       "      <th>上市时间</th>\n",
       "      <th>信用评级</th>\n",
       "      <th>symbol</th>\n",
       "      <th>market</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>127110</td>\n",
       "      <td>广核转债</td>\n",
       "      <td>2025-07-09</td>\n",
       "      <td>073816</td>\n",
       "      <td>003816</td>\n",
       "      <td>中国广核</td>\n",
       "      <td>49.00</td>\n",
       "      <td>2025-07-25</td>\n",
       "      <td>AAA</td>\n",
       "      <td>sz127110</td>\n",
       "      <td>sz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>113070</td>\n",
       "      <td>渝水转债</td>\n",
       "      <td>2025-01-09</td>\n",
       "      <td>783158</td>\n",
       "      <td>601158</td>\n",
       "      <td>重庆水务</td>\n",
       "      <td>19.00</td>\n",
       "      <td>2025-02-13</td>\n",
       "      <td>AAA</td>\n",
       "      <td>sh113070</td>\n",
       "      <td>sh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>113067</td>\n",
       "      <td>燃23转债</td>\n",
       "      <td>2023-07-27</td>\n",
       "      <td>783139</td>\n",
       "      <td>601139</td>\n",
       "      <td>深圳燃气</td>\n",
       "      <td>30.00</td>\n",
       "      <td>2023-08-18</td>\n",
       "      <td>AAA</td>\n",
       "      <td>sh113067</td>\n",
       "      <td>sh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>127084</td>\n",
       "      <td>柳工转2</td>\n",
       "      <td>2023-03-27</td>\n",
       "      <td>070528</td>\n",
       "      <td>000528</td>\n",
       "      <td>柳工</td>\n",
       "      <td>30.00</td>\n",
       "      <td>2023-04-20</td>\n",
       "      <td>AAA</td>\n",
       "      <td>sz127084</td>\n",
       "      <td>sz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>127083</td>\n",
       "      <td>山路转债</td>\n",
       "      <td>2023-03-24</td>\n",
       "      <td>070498</td>\n",
       "      <td>000498</td>\n",
       "      <td>山东路桥</td>\n",
       "      <td>48.36</td>\n",
       "      <td>2023-04-26</td>\n",
       "      <td>AAA</td>\n",
       "      <td>sz127083</td>\n",
       "      <td>sz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>982</th>\n",
       "      <td>126007</td>\n",
       "      <td>07日照债</td>\n",
       "      <td>2007-11-27</td>\n",
       "      <td>733017</td>\n",
       "      <td>600017</td>\n",
       "      <td>日照港</td>\n",
       "      <td>8.80</td>\n",
       "      <td>2007-12-12</td>\n",
       "      <td>AAA</td>\n",
       "      <td>sz126007</td>\n",
       "      <td>sz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>983</th>\n",
       "      <td>110227</td>\n",
       "      <td>赤化转债</td>\n",
       "      <td>2007-10-10</td>\n",
       "      <td>733227</td>\n",
       "      <td>600227</td>\n",
       "      <td>赤天化</td>\n",
       "      <td>4.50</td>\n",
       "      <td>2007-10-23</td>\n",
       "      <td>AAA</td>\n",
       "      <td>sh110227</td>\n",
       "      <td>sh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>984</th>\n",
       "      <td>126006</td>\n",
       "      <td>07深高债</td>\n",
       "      <td>2007-10-09</td>\n",
       "      <td>733548</td>\n",
       "      <td>600548</td>\n",
       "      <td>深高速</td>\n",
       "      <td>15.00</td>\n",
       "      <td>2007-10-30</td>\n",
       "      <td>AAA</td>\n",
       "      <td>sz126006</td>\n",
       "      <td>sz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>985</th>\n",
       "      <td>110971</td>\n",
       "      <td>恒源转债</td>\n",
       "      <td>2007-09-24</td>\n",
       "      <td>733971</td>\n",
       "      <td>600971</td>\n",
       "      <td>恒源煤电</td>\n",
       "      <td>4.00</td>\n",
       "      <td>2007-10-12</td>\n",
       "      <td>AAA</td>\n",
       "      <td>sh110971</td>\n",
       "      <td>sh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>987</th>\n",
       "      <td>110026</td>\n",
       "      <td>中海转债</td>\n",
       "      <td>2007-07-02</td>\n",
       "      <td>733026</td>\n",
       "      <td>600026</td>\n",
       "      <td>中远海能</td>\n",
       "      <td>20.00</td>\n",
       "      <td>2007-07-12</td>\n",
       "      <td>AAA</td>\n",
       "      <td>sh110026</td>\n",
       "      <td>sh</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       债券代码   债券简称        申购日期    申购代码    正股代码  正股简称   发行规模        上市时间 信用评级  \\\n",
       "10   127110   广核转债  2025-07-09  073816  003816  中国广核  49.00  2025-07-25  AAA   \n",
       "30   113070   渝水转债  2025-01-09  783158  601158  重庆水务  19.00  2025-02-13  AAA   \n",
       "129  113067  燃23转债  2023-07-27  783139  601139  深圳燃气  30.00  2023-08-18  AAA   \n",
       "180  127084   柳工转2  2023-03-27  070528  000528    柳工  30.00  2023-04-20  AAA   \n",
       "181  127083   山路转债  2023-03-24  070498  000498  山东路桥  48.36  2023-04-26  AAA   \n",
       "..      ...    ...         ...     ...     ...   ...    ...         ...  ...   \n",
       "982  126007  07日照债  2007-11-27  733017  600017   日照港   8.80  2007-12-12  AAA   \n",
       "983  110227   赤化转债  2007-10-10  733227  600227   赤天化   4.50  2007-10-23  AAA   \n",
       "984  126006  07深高债  2007-10-09  733548  600548   深高速  15.00  2007-10-30  AAA   \n",
       "985  110971   恒源转债  2007-09-24  733971  600971  恒源煤电   4.00  2007-10-12  AAA   \n",
       "987  110026   中海转债  2007-07-02  733026  600026  中远海能  20.00  2007-07-12  AAA   \n",
       "\n",
       "       symbol market  \n",
       "10   sz127110     sz  \n",
       "30   sh113070     sh  \n",
       "129  sh113067     sh  \n",
       "180  sz127084     sz  \n",
       "181  sz127083     sz  \n",
       "..        ...    ...  \n",
       "982  sz126007     sz  \n",
       "983  sh110227     sh  \n",
       "984  sz126006     sz  \n",
       "985  sh110971     sh  \n",
       "987  sh110026     sh  \n",
       "\n",
       "[100 rows x 11 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.query(\"信用评级=='AAA'\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "06c698e6-3e7e-4c69-a89f-0b276aed5e64",
   "metadata": {},
   "outputs": [],
   "source": [
    "data['申购日期'] = pd.to_datetime(data['申购日期'], errors='coerce')\n",
    "data['上市时间'] = pd.to_datetime(data['上市时间'], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "13993802-fd74-4846-9b85-da6fa5e464ae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "申购日期\n",
       "2020-07    31\n",
       "2019-12    31\n",
       "2023-07    30\n",
       "2020-12    28\n",
       "2019-03    24\n",
       "           ..\n",
       "2013-03     1\n",
       "2013-01     1\n",
       "2017-09     1\n",
       "2012-10     1\n",
       "2007-07     1\n",
       "Freq: M, Name: count, Length: 156, dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['申购日期'].dt.to_period('M').value_counts()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "f6c9a095-0546-4468-af9a-ee93cb9600b2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>债券代码</th>\n",
       "      <th>债券简称</th>\n",
       "      <th>申购日期</th>\n",
       "      <th>申购代码</th>\n",
       "      <th>正股代码</th>\n",
       "      <th>正股简称</th>\n",
       "      <th>发行规模</th>\n",
       "      <th>上市时间</th>\n",
       "      <th>信用评级</th>\n",
       "      <th>symbol</th>\n",
       "      <th>market</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>127110</td>\n",
       "      <td>广核转债</td>\n",
       "      <td>2025-07-09</td>\n",
       "      <td>073816</td>\n",
       "      <td>003816</td>\n",
       "      <td>中国广核</td>\n",
       "      <td>49.0</td>\n",
       "      <td>2025-07-25</td>\n",
       "      <td>AAA</td>\n",
       "      <td>sz127110</td>\n",
       "      <td>sz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>113070</td>\n",
       "      <td>渝水转债</td>\n",
       "      <td>2025-01-09</td>\n",
       "      <td>783158</td>\n",
       "      <td>601158</td>\n",
       "      <td>重庆水务</td>\n",
       "      <td>19.0</td>\n",
       "      <td>2025-02-13</td>\n",
       "      <td>AAA</td>\n",
       "      <td>sh113070</td>\n",
       "      <td>sh</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      债券代码  债券简称       申购日期    申购代码    正股代码  正股简称  发行规模       上市时间 信用评级  \\\n",
       "10  127110  广核转债 2025-07-09  073816  003816  中国广核  49.0 2025-07-25  AAA   \n",
       "30  113070  渝水转债 2025-01-09  783158  601158  重庆水务  19.0 2025-02-13  AAA   \n",
       "\n",
       "      symbol market  \n",
       "10  sz127110     sz  \n",
       "30  sh113070     sh  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.query(\"信用评级=='AAA' and 申购日期>='2024-01-01'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "852d11f8-c904-4b69-9674-cc3bc56c71b7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "信用评级\n",
       "A       3\n",
       "A+     10\n",
       "AA     11\n",
       "AA+     3\n",
       "AA-    14\n",
       "dtype: int64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[data['申购日期'].dt.year == 2024].groupby('信用评级').size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "03f78214-ef98-4304-bfd1-97d9cccd2544",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "41"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(data[data['申购日期'].dt.year == 2024])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "d907d808-c150-4f20-847e-b032af071c64",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "988"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.to_sql(\n",
    "    name='bond_zh_cov_base',\n",
    "    con=engine,\n",
    "    if_exists='replace'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "757f6487-7421-4ea7-8f33-1d7a385a1797",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
